package com.web.new12306.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.web.new12306.dao.UserDao;
import com.web.new12306.pojo.Ticket;
import com.web.new12306.pojo.User;


public class UserDaoImpl implements UserDao{
	Connection conn = null;
	PreparedStatement pstmt = null;
	Statement stmt = null;
	ResultSet rs = null;
	@Override
	/**
	 * @author 苗宇
	 */
	public boolean Login(Connection conn,String name, String pwd) {
		
		boolean flag=false;
		try {
			this.conn=conn;
			String sql = "select * from user where username=? and password=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,name);//
			pstmt.setString(2,pwd);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				flag=true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//this.closeAll(conn, pstmt, rs);
		}
		return flag;
	}

	@Override
	/**
	 * @author 苗宇
	 */
	public ArrayList<User> showAll(Connection conn) {
		ArrayList<User> list=new ArrayList<User>();
	   this.conn=conn;
		
		try {
			String sql="select * from user order by id ";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			    while (rs.next()) {
					User user=new User();
					user.setId(rs.getInt("id"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("password"));
					user.setSex(rs.getString("sex"));
					user.setCardType(rs.getString("cardtype"));
					user.setType(rs.getString("type"));
					list.add(user);
		} 
			    }catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//closeAll(conn, stmt, rs);
		
		
	}return list;
	}
	/**
	 * @author 苗宇
	 */
	public int excuteUpdate(Connection conn,String sql,Object param[]){
		int num=0;
		PreparedStatement pstmt=null;
		this.conn=conn;
		try {
			pstmt=conn.prepareStatement(sql);
			if(pstmt!=null){
				for (int i = 0; i < param.length; i++) {
					pstmt.setObject(i+1, param[i]);
				}
			}
			
			num=pstmt.executeUpdate();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
		//	closeAll(conn, pstmt, null);
		}
		return num;
		
	}
	/**
	 * @author 苗宇
	 */
	@Override
	public int saveUser(Connection conn,User user) {
		int num=0;
		this.conn=conn;
		String sql="insert into user(username,password,sex,cardtype,cardid,type,province,city,realname,content)" +
				"values(?,?,?,?,?,?,?,?,?,?)";
		Object []add={user.getUsername(),user.getPassword(),user.getSex(),user.getCardType(),user.getCardId(),user.getType(),user.getProvince(),user.getCity(),user.getRealname(),user.getContent()};
		try {
			pstmt=conn.prepareStatement(sql);
			if(pstmt!=null){
				for (int i = 0; i < add.length; i++) {
					pstmt.setObject(i+1, add[i]);
			}
				num=pstmt.executeUpdate();	
		}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return num;
	}
	
	/**
	 * @author 蔡艳
	 */
	public User showPerson(Connection conn, User user) {
		User user2=new User();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		this.conn = conn;
		String sql = "select * from user where username=?";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,user.getUsername());
			rs=pstmt.executeQuery();
			while (rs.next()) {
				user2.setId(rs.getInt("id"));
				user2.setUsername(rs.getString("username"));
				user2.setRealname(rs.getString("realname"));
				user2.setPassword(rs.getString("password"));
				user2.setSex(rs.getString("sex"));
				user2.setCardType(rs.getString("cardType"));
				user2.setCardId(rs.getString("cardId"));
				user2.setProvince(rs.getString("province"));
				user2.setCity(rs.getString("city"));
				user2.setType(rs.getString("type"));
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		//closeAll(conn, pstmt, rs);
		return user2;
	}

	/**
	 * @author 蔡艳
	 */
	public int updatePerson(Connection conn, User user) {
		int num=0;
		this.conn=conn;
		String sql="update user set username=?,realname=?,sex=?,province=?,city=?,cardType=?,cardId=?,type=? where id=?";
		
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, user.getUsername());
			pstmt.setString(2, user.getRealname());
			pstmt.setString(3, user.getSex());
			pstmt.setString(4, user.getProvince());
			pstmt.setString(5, user.getCity());
			pstmt.setString(6, user.getCardType());
			pstmt.setString(7, user.getCardId());
			pstmt.setString(8, user.getType());
			pstmt.setInt(9, user.getId());
			
			num=pstmt.executeUpdate();	
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return num;
	}
	/**
	 * 
	 * @anther苗宇
	 */
	@Override
	public int number(Connection conn) {
		// TODO Auto-generated method stub
		String sql="select count(*) from user";
		int n=0;
		try {
			this.conn=conn;
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				n=rs.getInt(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return n;
	}

	/**
	 * 
	 * @anther苗宇
	 */
	@Override
	public List<User> select(Connection conn, Integer page, Integer rows) {
		String sql = "select * from user limit ?,?";
		List<User> list = new ArrayList<User>();
		try {
			this.conn=conn;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, (page-1)*rows);
			pstmt.setInt(2, page*rows);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				User user=new User();
				user.setUsername(rs.getString("username"));
				user.setSex(rs.getString("sex"));
				user.setProvince(rs.getString("province"));
				user.setCity(rs.getString("city"));
				list.add(user);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
}
